package yunc.checkData.utils;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import yunc.checkData.pojo.HyBaseZjtide;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Properties;

public class JDBCUtils {
    private static Logger logger = LoggerFactory.getLogger(JDBCUtils.class.getName());

    /***
     * 返回一个数据库连接
     */
    public static Connection getConnection()
    {
        Connection connection = null;// 创建一个数据库连接
        Properties config = PropertiesUtil.load("config.properties");
        String url=config.getProperty("jdbc.url");
        String user=config.getProperty("jdbc.lggzw.user");
        String password=config.getProperty("jdbc.lggzw.password");
        try
        {
            connection = DriverManager.getConnection(url, user, password);// 获取连接
            return connection;
        } catch (Exception e)
        {
            logger.error("数据库连接失败！请检查配置");
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 提交查询sql语句
     * @param sql
     * @return
     */
    public static List<HashMap<String, String>> handExecuteQuery(String sql,List<String> params) {
        List<HashMap<String, String>> list = new ArrayList<>();
        Connection conn = getConnection();
        logger.info("本次执行的查询语句sql是： "+sql);
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            for (int i = 0; i < params.size(); i++) {
                logger.info("sql语句的第 " +i+ " 个参数是： " + params.get(i));
                pstmt.setString(i+1,params.get(i));
            }
            ResultSet resultSet = pstmt.executeQuery();
            list = getDataFromResultSet(resultSet);
            resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt!=null) pstmt.close();
                if (conn!=null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    private static List<HashMap<String, String>> getDataFromResultSet(ResultSet resultSet){
        ArrayList<HashMap<String, String>> list = new ArrayList<>();

        try {
            //获取表的元信息
            ResultSetMetaData metaData = resultSet.getMetaData();
            //将结果集中的数据取出来放到Map中,最后组合成list返回
            while (resultSet.next()) {
                HashMap<String, String> hashMap = new HashMap<String, String>();
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    String columnName = metaData.getColumnName(i);
                    logger.debug("本次进行操作的列名称是： "+columnName);
                    String columnValue = "";
                    if (resultSet.getObject(columnName) == null) {
                        columnValue = null;
                    } else {
                        columnValue = resultSet.getObject(columnName).toString();
                    }
                    hashMap.put(columnName, columnValue);
                }
                list.add(hashMap);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 执行批量插入语句
     * @param tableName
     * @param hyBaseZjtides
     * @return
     */
    public static Integer executeBatchUpdate(String tableName, List<HyBaseZjtide> hyBaseZjtides){
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        Integer updateNum=0;

        String sql = "insert into " + tableName +" (id,timestamp,stationname,cryear,crday,datatimes,depth_m,rbrpressureinfo,machineid,ums_ts_,input_time) values(?,?,?,?,?,?,?,?,?,?,now())";

        try {
            pstmt = conn.prepareStatement(sql);
            for (int i = 0; i < hyBaseZjtides.size(); i++) {
                HyBaseZjtide hyBaseZjtide = hyBaseZjtides.get(i);
                if(hyBaseZjtide != null){
                    pstmt.setString(1,MD5Util.md5(hyBaseZjtide.getTimestamp()+hyBaseZjtide.getStationname()));
                    pstmt.setTimestamp(2,Timestamp.valueOf(hyBaseZjtide.getTimestamp()));
                    pstmt.setString(3, hyBaseZjtide.getStationname());
                    if (hyBaseZjtide.getCrYear() != null){
                        pstmt.setInt(4, hyBaseZjtide.getCrYear());
                    }else{
                        pstmt.setNull(4,2);
                    }
                    if (hyBaseZjtide.getCrDay() != null){
                        pstmt.setInt(5, hyBaseZjtide.getCrDay());
                    }else{
                        pstmt.setNull(5,2);
                    }
                    if (hyBaseZjtide.getDataTimes() != null){
                        pstmt.setInt(6, hyBaseZjtide.getDataTimes());
                    }else{
                        pstmt.setNull(6,2);
                    }
                    if (hyBaseZjtide.getDepthM() != null){
                        pstmt.setInt(7, hyBaseZjtide.getDepthM());
                    }else{
                        pstmt.setNull(7,2);
                    }
                    if (hyBaseZjtide.getrBRPressureInfo() != null){
                        pstmt.setDouble(8, hyBaseZjtide.getrBRPressureInfo());
                    }else{
                        pstmt.setNull(8,2);
                    }
                    if (hyBaseZjtide.getMachineID() != null){
                        pstmt.setInt(9, hyBaseZjtide.getMachineID());
                    }else{
                        pstmt.setNull(9,2);
                    }
                    if (hyBaseZjtide.getUmsTs() != null){
                        pstmt.setString(10, hyBaseZjtide.getUmsTs());
                    }else{
                        pstmt.setNull(10,2);
                    }
                }

                pstmt.addBatch();
            }
            int[] ints = pstmt.executeBatch();
            for (int i = 0; i < ints.length; i++) {
                updateNum+=ints[i];
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt!=null) pstmt.close();
                if (conn!=null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return updateNum;
    }


    public static Integer executeUpdate(String sql, List<String> params){

        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        Integer updateNum=0;
        logger.info("本次执行的更新/删除/插入sql语句: " + sql);
        try {
            conn.setAutoCommit(false);
            pstmt = conn.prepareStatement(sql);
            for (int i = 0; i < params.size(); i++) {
                logger.info("sql语句的第 " +i+ " 个参数是： " + params.get(i));
                pstmt.setString(i+1,params.get(i));
            }
            updateNum = pstmt.executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                if (pstmt!=null) pstmt.close();
                if (conn!=null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return updateNum;
    }

}
